using MySqlConnector;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using System.Text.Json.Serialization;
using System.IO;
using System.Linq;
using System.Text;
using System.Security.Cryptography;

namespace FinData.Services
{
    // 帖子数据模型
    public class ForumPost
    {
        [JsonPropertyName("id")]
        public int Id { get; set; }
        
        [JsonPropertyName("userId")]
        public int? UserId { get; set; }
        
        [JsonPropertyName("username")]
        public string? Username { get; set; }
        
        [JsonPropertyName("title")]
        public required string Title { get; set; }
        
        [JsonPropertyName("content")]
        public required string Content { get; set; }
        
        [JsonPropertyName("viewCount")]
        public int ViewCount { get; set; }
        
        [JsonPropertyName("createdAt")]
        public DateTime CreatedAt { get; set; }
        
        [JsonPropertyName("updatedAt")]
        public DateTime UpdatedAt { get; set; }
        
        [JsonPropertyName("commentCount")]
        public int CommentCount { get; set; }
        
        [JsonPropertyName("likeCount")]
        public int LikeCount { get; set; }
        
        [JsonPropertyName("isLiked")]
        public bool IsLiked { get; set; }
        
        [JsonPropertyName("tags")]
        public List<string>? Tags { get; set; }
        
        [JsonPropertyName("images")]
        public List<ForumImage>? Images { get; set; }
    }

    // 评论数据模型
    public class ForumComment
    {
        [JsonPropertyName("id")]
        public int Id { get; set; }
        
        [JsonPropertyName("postId")]
        public int PostId { get; set; }
        
        [JsonPropertyName("userId")]
        public int UserId { get; set; }
        
        [JsonPropertyName("username")]
        public required string Username { get; set; }
        
        [JsonPropertyName("content")]
        public required string Content { get; set; }
        
        [JsonPropertyName("createdAt")]
        public DateTime CreatedAt { get; set; }
    }

    // 举报数据模型
    public class ForumReport
    {
        [JsonPropertyName("id")]
        public int Id { get; set; }
        
        [JsonPropertyName("postId")]
        public int PostId { get; set; }
        
        [JsonPropertyName("userId")]
        public int UserId { get; set; }
        
        [JsonPropertyName("username")]
        public string? Username { get; set; }
        
        [JsonPropertyName("reason")]
        public string Reason { get; set; }
        
        [JsonPropertyName("detail")]
        public string? Detail { get; set; }
        
        [JsonPropertyName("status")]
        public string Status { get; set; }
        
        [JsonPropertyName("createdAt")]
        public DateTime CreatedAt { get; set; }
        
        [JsonPropertyName("updatedAt")]
        public DateTime UpdatedAt { get; set; }
    }

    // 图片数据模型
    public class ImageData
    {
        [JsonPropertyName("name")]
        public string Name { get; set; } = "";
        
        [JsonPropertyName("data")]
        public string Data { get; set; } = "";
        
        [JsonPropertyName("type")]
        public string Type { get; set; } = "";
    }

    // 帖子图片数据模型
    public class ForumImage
    {
        [JsonPropertyName("id")]
        public int Id { get; set; }
        
        [JsonPropertyName("postId")]
        public int PostId { get; set; }
        
        [JsonPropertyName("imagePath")]
        public required string ImagePath { get; set; }
        
        [JsonPropertyName("createdAt")]
        public DateTime CreatedAt { get; set; }
    }

    /// <summary>
    /// 用户惩罚记录
    /// </summary>
    public class UserPunishment
    {
        [JsonPropertyName("id")]
        public int Id { get; set; }

        [JsonPropertyName("userId")]
        public int UserId { get; set; }

        [JsonPropertyName("punishmentType")]
        public string PunishmentType { get; set; }

        [JsonPropertyName("startDate")]
        public DateTime StartDate { get; set; }

        [JsonPropertyName("endDate")]
        public DateTime EndDate { get; set; }

        [JsonPropertyName("reason")]
        public string? Reason { get; set; }

        [JsonPropertyName("relatedReportId")]
        public int? RelatedReportId { get; set; }

        [JsonPropertyName("createdBy")]
        public int CreatedBy { get; set; }

        [JsonPropertyName("adminName")]
        public string? AdminName { get; set; }

        [JsonPropertyName("createdAt")]
        public DateTime CreatedAt { get; set; }

        [JsonPropertyName("isActive")]
        public bool IsActive { get; set; }

        [JsonPropertyName("durationDays")]
        public int DurationDays => (int)(EndDate - StartDate).TotalDays;
    }

    /// <summary>
    /// 用户惩罚状态
    /// </summary>
    public class UserPunishmentStatus
    {
        [JsonPropertyName("isMuted")]
        public bool IsMuted { get; set; }

        [JsonPropertyName("isBanned")]
        public bool IsBanned { get; set; }

        [JsonPropertyName("muteEndDate")]
        public DateTime? MuteEndDate { get; set; }

        [JsonPropertyName("banEndDate")]
        public DateTime? BanEndDate { get; set; }

        [JsonPropertyName("muteReason")]
        public string? MuteReason { get; set; }

        [JsonPropertyName("banReason")]
        public string? BanReason { get; set; }
    }

    public class ForumService
    {
        private readonly string _connectionString;

        public ForumService()
        {
            // 配置数据库连接字符串
            _connectionString = "Server=localhost;Port=3306;Database=findata;User=root;Password=root;";
        }

        /// <summary>
        /// 获取数据库连接
        /// </summary>
        private MySqlConnection GetConnection()
        {
            return new MySqlConnection(_connectionString);
        }

        /// <summary>
        /// 获取帖子列表，按最新创建时间排序
        /// </summary>
        public async Task<List<ForumPost>> GetPostsAsync(int page = 1, int pageSize = 10, string sortBy = "newest")
        {
            var posts = new List<ForumPost>();
            int offset = (page - 1) * pageSize;

            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();

                    // 根据排序方式构建不同的排序条件
                    string orderBy = sortBy switch
                    {
                        "likes" => "(SELECT COUNT(*) FROM forum_likes WHERE post_id = p.id) DESC, p.created_at DESC",
                        "comments" => "(SELECT COUNT(*) FROM forum_comments WHERE post_id = p.id) DESC, p.created_at DESC",
                        "views" => "p.view_count DESC, p.created_at DESC",
                        "hot" => "((SELECT COUNT(*) FROM forum_likes WHERE post_id = p.id) + (SELECT COUNT(*) FROM forum_comments WHERE post_id = p.id) * 2) DESC, p.created_at DESC",
                        _ => "p.created_at DESC" // 默认按最新排序
                    };

                    string query = $@"
                        SELECT 
                            p.id, p.user_id, u.username, p.title, 
                            SUBSTRING(p.content, 1, 200) as content, 
                            p.view_count, p.created_at, p.updated_at,
                            (SELECT COUNT(*) FROM forum_comments WHERE post_id = p.id) as comment_count,
                            (SELECT COUNT(*) FROM forum_likes WHERE post_id = p.id) as like_count
                        FROM 
                            forum_posts p
                        LEFT JOIN 
                            users u ON p.user_id = u.id
                        ORDER BY 
                            {orderBy}
                        LIMIT @pageSize OFFSET @offset";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@pageSize", pageSize);
                        cmd.Parameters.AddWithValue("@offset", offset);

                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                posts.Add(new ForumPost
                                {
                                    Id = reader.GetInt32("id"),
                                    UserId = reader.IsDBNull("user_id") ? null : reader.GetInt32("user_id"),
                                    Username = reader.IsDBNull("username") ? "匿名用户" : reader.GetString("username"),
                                    Title = reader.GetString("title"),
                                    Content = reader.GetString("content"),
                                    ViewCount = reader.GetInt32("view_count"),
                                    CreatedAt = reader.GetDateTime("created_at"),
                                    UpdatedAt = reader.GetDateTime("updated_at"),
                                    CommentCount = reader.GetInt32("comment_count"),
                                    LikeCount = reader.GetInt32("like_count"),
                                    IsLiked = false
                                });
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取帖子列表失败: {ex.Message}");
                throw;
            }

            return posts;
        }

        /// <summary>
        /// 获取热门帖子Top N
        /// </summary>
        public async Task<List<ForumPost>> GetHotPostsAsync(int count = 5)
        {
            var posts = new List<ForumPost>();

            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();

                    string query = @"
                        SELECT 
                            p.id, p.user_id, u.username, p.title, 
                            SUBSTRING(p.content, 1, 100) as content, 
                            p.view_count, p.created_at, p.updated_at,
                            (SELECT COUNT(*) FROM forum_comments WHERE post_id = p.id) as comment_count,
                            (SELECT COUNT(*) FROM forum_likes WHERE post_id = p.id) as like_count,
                            ((SELECT COUNT(*) FROM forum_likes WHERE post_id = p.id) + 
                             (SELECT COUNT(*) FROM forum_comments WHERE post_id = p.id) * 2) as heat_value
                        FROM 
                            forum_posts p
                        LEFT JOIN 
                            users u ON p.user_id = u.id
                        ORDER BY 
                            heat_value DESC, p.created_at DESC
                        LIMIT @count";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@count", count);

                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                posts.Add(new ForumPost
                                {
                                    Id = reader.GetInt32("id"),
                                    UserId = reader.IsDBNull("user_id") ? null : reader.GetInt32("user_id"),
                                    Username = reader.IsDBNull("username") ? "匿名用户" : reader.GetString("username"),
                                    Title = reader.GetString("title"),
                                    Content = reader.GetString("content"),
                                    ViewCount = reader.GetInt32("view_count"),
                                    CreatedAt = reader.GetDateTime("created_at"),
                                    UpdatedAt = reader.GetDateTime("updated_at"),
                                    CommentCount = reader.GetInt32("comment_count"),
                                    LikeCount = reader.GetInt32("like_count"),
                                    IsLiked = false
                                });
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取热门帖子失败: {ex.Message}");
                throw;
            }

            return posts;
        }

        /// <summary>
        /// 获取帖子详情
        /// </summary>
        public async Task<ForumPost?> GetPostByIdAsync(int postId, int? currentUserId = null)
        {
            ForumPost? post = null;

            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();

                    // 更新浏览量
                    string updateViewQuery = "UPDATE forum_posts SET view_count = view_count + 1 WHERE id = @postId";
                    using (var cmd = new MySqlCommand(updateViewQuery, connection))
                    {
                        cmd.Parameters.AddWithValue("@postId", postId);
                        await cmd.ExecuteNonQueryAsync();
                    }

                    // 获取帖子详情
                    string query = @"
                        SELECT 
                            p.id, p.user_id, u.username, p.title, p.content, 
                            p.view_count, p.created_at, p.updated_at,
                            (SELECT COUNT(*) FROM forum_comments WHERE post_id = p.id) as comment_count,
                            (SELECT COUNT(*) FROM forum_likes WHERE post_id = p.id) as like_count
                        FROM 
                            forum_posts p
                        LEFT JOIN 
                            users u ON p.user_id = u.id
                        WHERE 
                            p.id = @postId";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@postId", postId);

                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            if (await reader.ReadAsync())
                            {
                                post = new ForumPost
                                {
                                    Id = reader.GetInt32("id"),
                                    UserId = reader.IsDBNull("user_id") ? null : reader.GetInt32("user_id"),
                                    Username = reader.IsDBNull("username") ? "匿名用户" : reader.GetString("username"),
                                    Title = reader.GetString("title"),
                                    Content = reader.GetString("content"),
                                    ViewCount = reader.GetInt32("view_count"),
                                    CreatedAt = reader.GetDateTime("created_at"),
                                    UpdatedAt = reader.GetDateTime("updated_at"),
                                    CommentCount = reader.GetInt32("comment_count"),
                                    LikeCount = reader.GetInt32("like_count"),
                                    IsLiked = false,
                                    Tags = new List<string>()
                                };
                            }
                        }
                    }

                    // 如果找到帖子，获取标签
                    if (post != null)
                    {
                        post.Tags = await GetPostTagsAsync(postId);
                        
                        // 获取帖子图片
                        var images = await GetPostImagesAsync(postId);
                        post.Images = images;

                        // 检查当前用户是否点赞
                        if (currentUserId.HasValue)
                        {
                            post.IsLiked = await IsLikedAsync(postId, currentUserId.Value);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取帖子详情失败: {ex.Message}");
                throw;
            }

            return post;
        }

        /// <summary>
        /// 创建新帖子（不带标签）
        /// </summary>
        public async Task<int> CreatePostAsync(string title, string content, int? userId = null)
        {
            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();

                    string query = @"
                        INSERT INTO forum_posts (user_id, title, content)
                        VALUES (@userId, @title, @content);
                        SELECT LAST_INSERT_ID();";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@userId", userId == null ? DBNull.Value : userId);
                        cmd.Parameters.AddWithValue("@title", title);
                        cmd.Parameters.AddWithValue("@content", content);

                        var result = await cmd.ExecuteScalarAsync();
                        return Convert.ToInt32(result);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"创建帖子失败: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 创建新帖子（带标签）
        /// </summary>
        public async Task<int> CreatePostWithTagsAsync(string title, string content, List<string> tags, int? userId = null)
        {
            int postId = 0;
            
            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();
                    
                    // 开始事务
                    using (var transaction = await connection.BeginTransactionAsync())
                    {
                        try
                        {
                            // 创建帖子
                            string postQuery = @"
                                INSERT INTO forum_posts (user_id, title, content)
                                VALUES (@userId, @title, @content);
                                SELECT LAST_INSERT_ID();";

                            using (var cmd = new MySqlCommand(postQuery, connection, transaction))
                            {
                                cmd.Parameters.AddWithValue("@userId", userId == null ? DBNull.Value : userId);
                                cmd.Parameters.AddWithValue("@title", title);
                                cmd.Parameters.AddWithValue("@content", content);

                                var result = await cmd.ExecuteScalarAsync();
                                postId = Convert.ToInt32(result);
                            }

                            // 确保forum_tags表存在
                            string createTagsTableQuery = @"
                                CREATE TABLE IF NOT EXISTS forum_tags (
                                    id INT PRIMARY KEY AUTO_INCREMENT,
                                    name VARCHAR(50) NOT NULL UNIQUE,
                                    count INT DEFAULT 1,
                                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                                )";
                            
                            using (var cmd = new MySqlCommand(createTagsTableQuery, connection, transaction))
                            {
                                await cmd.ExecuteNonQueryAsync();
                            }
                            
                            // 确保post_tags关联表存在
                            string createPostTagsTableQuery = @"
                                CREATE TABLE IF NOT EXISTS forum_post_tags (
                                    post_id INT NOT NULL,
                                    tag_id INT NOT NULL,
                                    PRIMARY KEY (post_id, tag_id),
                                    FOREIGN KEY (post_id) REFERENCES forum_posts(id) ON DELETE CASCADE,
                                    FOREIGN KEY (tag_id) REFERENCES forum_tags(id) ON DELETE CASCADE
                                )";
                            
                            using (var cmd = new MySqlCommand(createPostTagsTableQuery, connection, transaction))
                            {
                                await cmd.ExecuteNonQueryAsync();
                            }

                            // 处理每个标签
                            if (tags != null && tags.Count > 0)
                            {
                                foreach (var tagName in tags)
                                {
                                    if (string.IsNullOrWhiteSpace(tagName))
                                        continue;
                                    
                                    string normalizedTag = tagName.Trim().ToLower();
                                    if (normalizedTag.Length > 50)
                                        normalizedTag = normalizedTag.Substring(0, 50);
                                    
                                    // 查找或创建标签
                                    int tagId;
                                    
                                    string findTagQuery = "SELECT id FROM forum_tags WHERE name = @name";
                                    using (var cmd = new MySqlCommand(findTagQuery, connection, transaction))
                                    {
                                        cmd.Parameters.AddWithValue("@name", normalizedTag);
                                        var existingTagId = await cmd.ExecuteScalarAsync();
                                        
                                        if (existingTagId != null && existingTagId != DBNull.Value)
                                        {
                                            // 标签已存在，更新计数
                                            tagId = Convert.ToInt32(existingTagId);
                                            
                                            string updateTagCountQuery = "UPDATE forum_tags SET count = count + 1 WHERE id = @id";
                                            using (var updateCmd = new MySqlCommand(updateTagCountQuery, connection, transaction))
                                            {
                                                updateCmd.Parameters.AddWithValue("@id", tagId);
                                                await updateCmd.ExecuteNonQueryAsync();
                                            }
                                        }
                                        else
                                        {
                                            // 创建新标签
                                            string createTagQuery = @"
                                                INSERT INTO forum_tags (name, count) VALUES (@name, 1);
                                                SELECT LAST_INSERT_ID();";
                                            
                                            using (var createCmd = new MySqlCommand(createTagQuery, connection, transaction))
                                            {
                                                createCmd.Parameters.AddWithValue("@name", normalizedTag);
                                                var result = await createCmd.ExecuteScalarAsync();
                                                tagId = Convert.ToInt32(result);
                                            }
                                        }
                                    }
                                    
                                    // 关联帖子和标签
                                    string linkTagQuery = "INSERT INTO forum_post_tags (post_id, tag_id) VALUES (@postId, @tagId)";
                                    using (var cmd = new MySqlCommand(linkTagQuery, connection, transaction))
                                    {
                                        cmd.Parameters.AddWithValue("@postId", postId);
                                        cmd.Parameters.AddWithValue("@tagId", tagId);
                                        await cmd.ExecuteNonQueryAsync();
                                    }
                                }
                            }
                            
                            // 提交事务
                            await transaction.CommitAsync();
                        }
                        catch (Exception ex)
                        {
                            // 回滚事务
                            await transaction.RollbackAsync();
                            Console.WriteLine($"创建带标签的帖子失败: {ex.Message}");
                            throw;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"创建带标签的帖子失败: {ex.Message}");
                throw;
            }
            
            return postId;
        }

        /// <summary>
        /// 获取帖子标签
        /// </summary>
        public async Task<List<string>> GetPostTagsAsync(int postId)
        {
            var tags = new List<string>();
            
            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();
                    
                    string query = @"
                        SELECT t.name
                        FROM forum_tags t
                        JOIN forum_post_tags pt ON t.id = pt.tag_id
                        WHERE pt.post_id = @postId
                        ORDER BY t.name";
                    
                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@postId", postId);
                        
                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                tags.Add(reader.GetString("name"));
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取帖子标签失败: {ex.Message}");
                throw;
            }
            
            return tags;
        }

        /// <summary>
        /// 按标签搜索帖子
        /// </summary>
        public async Task<List<ForumPost>> SearchPostsByTagAsync(string tag)
        {
            var posts = new List<ForumPost>();
            
            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();
                    
                    string query = @"
                        SELECT 
                            p.id, p.user_id, u.username, p.title, 
                            SUBSTRING(p.content, 1, 200) as content, 
                            p.view_count, p.created_at, p.updated_at,
                            (SELECT COUNT(*) FROM forum_comments WHERE post_id = p.id) as comment_count,
                            (SELECT COUNT(*) FROM forum_likes WHERE post_id = p.id) as like_count
                        FROM 
                            forum_posts p
                        JOIN 
                            forum_post_tags pt ON p.id = pt.post_id
                        JOIN 
                            forum_tags t ON pt.tag_id = t.id
                        LEFT JOIN 
                            users u ON p.user_id = u.id
                        WHERE 
                            t.name = @tag
                        ORDER BY 
                            p.created_at DESC
                        LIMIT 50";
                    
                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@tag", tag.Trim().ToLower());
                        
                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                posts.Add(new ForumPost
                                {
                                    Id = reader.GetInt32("id"),
                                    UserId = reader.IsDBNull("user_id") ? null : reader.GetInt32("user_id"),
                                    Username = reader.IsDBNull("username") ? "匿名用户" : reader.GetString("username"),
                                    Title = reader.GetString("title"),
                                    Content = reader.GetString("content"),
                                    ViewCount = reader.GetInt32("view_count"),
                                    CreatedAt = reader.GetDateTime("created_at"),
                                    UpdatedAt = reader.GetDateTime("updated_at"),
                                    CommentCount = reader.GetInt32("comment_count"),
                                    LikeCount = reader.GetInt32("like_count"),
                                    IsLiked = false
                                });
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"按标签搜索帖子失败: {ex.Message}");
                throw;
            }
            
            return posts;
        }

        /// <summary>
        /// 获取热门标签
        /// </summary>
        public async Task<List<KeyValuePair<string, int>>> GetHotTagsAsync(int count = 5)
        {
            var tags = new List<KeyValuePair<string, int>>();
            
            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();
                    
                    string query = @"
                        SELECT name, count
                        FROM forum_tags
                        ORDER BY count DESC
                        LIMIT @count";
                    
                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@count", count);
                        
                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                string tagName = reader.GetString("name");
                                int tagCount = reader.GetInt32("count");
                                tags.Add(new KeyValuePair<string, int>(tagName, tagCount));
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取热门标签失败: {ex.Message}");
                throw;
            }
            
            return tags;
        }
        
        /// <summary>
        /// 高级搜索：分别搜索标题、标签和内容
        /// </summary>
        public async Task<Dictionary<string, List<ForumPost>>> AdvancedSearchAsync(string keyword)
        {
            var result = new Dictionary<string, List<ForumPost>>
            {
                { "title", new List<ForumPost>() },
                { "tag", new List<ForumPost>() },
                { "content", new List<ForumPost>() }
            };
            
            if (string.IsNullOrWhiteSpace(keyword))
                return result;
                
            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();
                    
                    // 1. 搜索标题
                    string titleQuery = @"
                        SELECT 
                            p.id, p.user_id, u.username, p.title, 
                            SUBSTRING(p.content, 1, 200) as content, 
                            p.view_count, p.created_at, p.updated_at,
                            (SELECT COUNT(*) FROM forum_comments WHERE post_id = p.id) as comment_count,
                            (SELECT COUNT(*) FROM forum_likes WHERE post_id = p.id) as like_count
                        FROM 
                            forum_posts p
                        LEFT JOIN 
                            users u ON p.user_id = u.id
                        WHERE 
                            p.title LIKE @keyword
                        ORDER BY 
                            p.created_at DESC
                        LIMIT 20";
                    
                    using (var cmd = new MySqlCommand(titleQuery, connection))
                    {
                        cmd.Parameters.AddWithValue("@keyword", $"%{keyword}%");
                        
                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                result["title"].Add(new ForumPost
                                {
                                    Id = reader.GetInt32("id"),
                                    UserId = reader.IsDBNull("user_id") ? null : reader.GetInt32("user_id"),
                                    Username = reader.IsDBNull("username") ? "匿名用户" : reader.GetString("username"),
                                    Title = reader.GetString("title"),
                                    Content = reader.GetString("content"),
                                    ViewCount = reader.GetInt32("view_count"),
                                    CreatedAt = reader.GetDateTime("created_at"),
                                    UpdatedAt = reader.GetDateTime("updated_at"),
                                    CommentCount = reader.GetInt32("comment_count"),
                                    LikeCount = reader.GetInt32("like_count"),
                                    IsLiked = false
                                });
                            }
                        }
                    }
                    
                    // 2. 搜索标签
                    string tagQuery = @"
                        SELECT 
                            p.id, p.user_id, u.username, p.title, 
                            SUBSTRING(p.content, 1, 200) as content, 
                            p.view_count, p.created_at, p.updated_at,
                            (SELECT COUNT(*) FROM forum_comments WHERE post_id = p.id) as comment_count,
                            (SELECT COUNT(*) FROM forum_likes WHERE post_id = p.id) as like_count
                        FROM 
                            forum_posts p
                        JOIN 
                            forum_post_tags pt ON p.id = pt.post_id
                        JOIN 
                            forum_tags t ON pt.tag_id = t.id
                        LEFT JOIN 
                            users u ON p.user_id = u.id
                        WHERE 
                            t.name LIKE @keyword
                        ORDER BY 
                            p.created_at DESC
                        LIMIT 20";
                    
                    using (var cmd = new MySqlCommand(tagQuery, connection))
                    {
                        cmd.Parameters.AddWithValue("@keyword", $"%{keyword}%");
                        
                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                result["tag"].Add(new ForumPost
                                {
                                    Id = reader.GetInt32("id"),
                                    UserId = reader.IsDBNull("user_id") ? null : reader.GetInt32("user_id"),
                                    Username = reader.IsDBNull("username") ? "匿名用户" : reader.GetString("username"),
                                    Title = reader.GetString("title"),
                                    Content = reader.GetString("content"),
                                    ViewCount = reader.GetInt32("view_count"),
                                    CreatedAt = reader.GetDateTime("created_at"),
                                    UpdatedAt = reader.GetDateTime("updated_at"),
                                    CommentCount = reader.GetInt32("comment_count"),
                                    LikeCount = reader.GetInt32("like_count"),
                                    IsLiked = false
                                });
                            }
                        }
                    }
                    
                    // 3. 搜索内容
                    string contentQuery = @"
                        SELECT 
                            p.id, p.user_id, u.username, p.title, 
                            SUBSTRING(p.content, 1, 200) as content, 
                            p.view_count, p.created_at, p.updated_at,
                            (SELECT COUNT(*) FROM forum_comments WHERE post_id = p.id) as comment_count,
                            (SELECT COUNT(*) FROM forum_likes WHERE post_id = p.id) as like_count
                        FROM 
                            forum_posts p
                        LEFT JOIN 
                            users u ON p.user_id = u.id
                        WHERE 
                            p.content LIKE @keyword AND p.id NOT IN (SELECT id FROM forum_posts WHERE title LIKE @keyword)
                        ORDER BY 
                            p.created_at DESC
                        LIMIT 20";
                    
                    using (var cmd = new MySqlCommand(contentQuery, connection))
                    {
                        cmd.Parameters.AddWithValue("@keyword", $"%{keyword}%");
                        
                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                result["content"].Add(new ForumPost
                                {
                                    Id = reader.GetInt32("id"),
                                    UserId = reader.IsDBNull("user_id") ? null : reader.GetInt32("user_id"),
                                    Username = reader.IsDBNull("username") ? "匿名用户" : reader.GetString("username"),
                                    Title = reader.GetString("title"),
                                    Content = reader.GetString("content"),
                                    ViewCount = reader.GetInt32("view_count"),
                                    CreatedAt = reader.GetDateTime("created_at"),
                                    UpdatedAt = reader.GetDateTime("updated_at"),
                                    CommentCount = reader.GetInt32("comment_count"),
                                    LikeCount = reader.GetInt32("like_count"),
                                    IsLiked = false
                                });
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"高级搜索失败: {ex.Message}");
                throw;
            }
            
            return result;
        }

        /// <summary>
        /// 获取帖子评论
        /// </summary>
        public async Task<List<ForumComment>> GetCommentsAsync(int postId)
        {
            var comments = new List<ForumComment>();

            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();

                    string query = @"
                        SELECT 
                            c.id, c.post_id, c.user_id, u.username, c.content, c.created_at
                        FROM 
                            forum_comments c
                        JOIN 
                            users u ON c.user_id = u.id
                        WHERE 
                            c.post_id = @postId
                        ORDER BY 
                            c.created_at ASC";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@postId", postId);

                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                comments.Add(new ForumComment
                                {
                                    Id = reader.GetInt32("id"),
                                    PostId = reader.GetInt32("post_id"),
                                    UserId = reader.GetInt32("user_id"),
                                    Username = reader.GetString("username"),
                                    Content = reader.GetString("content"),
                                    CreatedAt = reader.GetDateTime("created_at")
                                });
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取评论失败: {ex.Message}");
                throw;
            }

            return comments;
        }

        /// <summary>
        /// 添加评论
        /// </summary>
        public async Task<int> AddCommentAsync(int postId, int userId, string content)
        {
            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();

                    string query = @"
                        INSERT INTO forum_comments (post_id, user_id, content)
                        VALUES (@postId, @userId, @content);
                        SELECT LAST_INSERT_ID();";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@postId", postId);
                        cmd.Parameters.AddWithValue("@userId", userId);
                        cmd.Parameters.AddWithValue("@content", content);

                        var result = await cmd.ExecuteScalarAsync();
                        return Convert.ToInt32(result);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"添加评论失败: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 点赞或取消点赞
        /// </summary>
        public async Task<bool> ToggleLikeAsync(int postId, int userId)
        {
            bool isLiked = false;

            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();

                    // 检查是否已点赞
                    string checkQuery = @"
                        SELECT COUNT(*) FROM forum_likes 
                        WHERE post_id = @postId AND user_id = @userId";

                    using (var cmd = new MySqlCommand(checkQuery, connection))
                    {
                        cmd.Parameters.AddWithValue("@postId", postId);
                        cmd.Parameters.AddWithValue("@userId", userId);

                        int count = Convert.ToInt32(await cmd.ExecuteScalarAsync());
                        
                        if (count > 0)
                        {
                            // 已点赞，取消点赞
                            string deleteQuery = @"
                                DELETE FROM forum_likes 
                                WHERE post_id = @postId AND user_id = @userId";

                            using (var deleteCmd = new MySqlCommand(deleteQuery, connection))
                            {
                                deleteCmd.Parameters.AddWithValue("@postId", postId);
                                deleteCmd.Parameters.AddWithValue("@userId", userId);
                                await deleteCmd.ExecuteNonQueryAsync();
                            }
                            isLiked = false;
                        }
                        else
                        {
                            // 未点赞，添加点赞
                            string insertQuery = @"
                                INSERT INTO forum_likes (post_id, user_id)
                                VALUES (@postId, @userId)";

                            using (var insertCmd = new MySqlCommand(insertQuery, connection))
                            {
                                insertCmd.Parameters.AddWithValue("@postId", postId);
                                insertCmd.Parameters.AddWithValue("@userId", userId);
                                await insertCmd.ExecuteNonQueryAsync();
                            }
                            isLiked = true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"点赞操作失败: {ex.Message}");
                throw;
            }

            return isLiked;
        }

        /// <summary>
        /// 获取帖子总数
        /// </summary>
        public async Task<int> GetPostCountAsync()
        {
            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();

                    string query = "SELECT COUNT(*) FROM forum_posts";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        return Convert.ToInt32(await cmd.ExecuteScalarAsync());
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取帖子总数失败: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 检查用户是否已点赞某帖子
        /// </summary>
        public async Task<bool> IsLikedAsync(int postId, int userId)
        {
            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();

                    string query = "SELECT COUNT(*) FROM forum_likes WHERE post_id = @postId AND user_id = @userId";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@postId", postId);
                        cmd.Parameters.AddWithValue("@userId", userId);

                        var result = await cmd.ExecuteScalarAsync();
                        return Convert.ToInt32(result) > 0;
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"检查点赞状态失败: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 举报帖子
        /// </summary>
        public async Task<int> ReportPostAsync(int postId, int userId, string reason, string detail)
        {
            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();

                    // 创建帖子举报表（如果不存在）
                    string createTableQuery = @"
                        CREATE TABLE IF NOT EXISTS forum_reports (
                            id INT PRIMARY KEY AUTO_INCREMENT,
                            post_id INT NOT NULL,
                            user_id INT NOT NULL,
                            reason VARCHAR(100) NOT NULL,
                            detail TEXT,
                            status VARCHAR(20) DEFAULT 'pending',
                            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                            FOREIGN KEY (post_id) REFERENCES forum_posts(id) ON DELETE CASCADE,
                            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
                        )";

                    using (var cmd = new MySqlCommand(createTableQuery, connection))
                    {
                        await cmd.ExecuteNonQueryAsync();
                    }

                    // 插入举报记录
                    string query = @"
                        INSERT INTO forum_reports (post_id, user_id, reason, detail)
                        VALUES (@postId, @userId, @reason, @detail);
                        SELECT LAST_INSERT_ID();";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@postId", postId);
                        cmd.Parameters.AddWithValue("@userId", userId);
                        cmd.Parameters.AddWithValue("@reason", reason);
                        cmd.Parameters.AddWithValue("@detail", detail);

                        var result = await cmd.ExecuteScalarAsync();
                        return Convert.ToInt32(result);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"举报帖子失败: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 获取举报列表（管理员用）
        /// </summary>
        public async Task<List<ForumReport>> GetReportsAsync(string status = "all", int page = 1, int pageSize = 20)
        {
            var reports = new List<ForumReport>();
            int offset = (page - 1) * pageSize;

            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();

                    string statusFilter = status != "all" ? "WHERE r.status = @status" : "";
                    
                    string query = $@"
                        SELECT 
                            r.id, r.post_id, r.user_id, u.username, r.reason, 
                            r.detail, r.status, r.created_at, r.updated_at
                        FROM 
                            forum_reports r
                        LEFT JOIN 
                            users u ON r.user_id = u.id
                        {statusFilter}
                        ORDER BY 
                            r.created_at DESC
                        LIMIT @pageSize OFFSET @offset";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        if (status != "all")
                            cmd.Parameters.AddWithValue("@status", status);
                        
                        cmd.Parameters.AddWithValue("@pageSize", pageSize);
                        cmd.Parameters.AddWithValue("@offset", offset);

                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                reports.Add(new ForumReport
                                {
                                    Id = reader.GetInt32("id"),
                                    PostId = reader.GetInt32("post_id"),
                                    UserId = reader.GetInt32("user_id"),
                                    Username = reader.IsDBNull("username") ? null : reader.GetString("username"),
                                    Reason = reader.GetString("reason"),
                                    Detail = reader.IsDBNull("detail") ? null : reader.GetString("detail"),
                                    Status = reader.GetString("status"),
                                    CreatedAt = reader.GetDateTime("created_at"),
                                    UpdatedAt = reader.GetDateTime("updated_at")
                                });
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取举报列表失败: {ex.Message}");
                throw;
            }

            return reports;
        }

        /// <summary>
        /// 更新举报状态（管理员用）
        /// </summary>
        public async Task<bool> UpdateReportStatusAsync(int reportId, string status)
        {
            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();

                    string query = @"
                        UPDATE forum_reports 
                        SET status = @status
                        WHERE id = @reportId";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@reportId", reportId);
                        cmd.Parameters.AddWithValue("@status", status);

                        int rowsAffected = await cmd.ExecuteNonQueryAsync();
                        return rowsAffected > 0;
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"更新举报状态失败: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 搜索帖子
        /// </summary>
        public async Task<List<ForumPost>> SearchPostsAsync(string keyword)
        {
            var posts = new List<ForumPost>();

            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();

                    string query = @"
                        SELECT 
                            p.id, p.user_id, u.username, p.title, 
                            SUBSTRING(p.content, 1, 200) as content, 
                            p.view_count, p.created_at, p.updated_at,
                            (SELECT COUNT(*) FROM forum_comments WHERE post_id = p.id) as comment_count,
                            (SELECT COUNT(*) FROM forum_likes WHERE post_id = p.id) as like_count
                        FROM 
                            forum_posts p
                        LEFT JOIN 
                            users u ON p.user_id = u.id
                        WHERE 
                            p.title LIKE @keyword OR p.content LIKE @keyword
                        ORDER BY 
                            p.created_at DESC
                        LIMIT 50";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@keyword", $"%{keyword}%");

                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                posts.Add(new ForumPost
                                {
                                    Id = reader.GetInt32("id"),
                                    UserId = reader.IsDBNull("user_id") ? null : reader.GetInt32("user_id"),
                                    Username = reader.IsDBNull("username") ? "匿名用户" : reader.GetString("username"),
                                    Title = reader.GetString("title"),
                                    Content = reader.GetString("content"),
                                    ViewCount = reader.GetInt32("view_count"),
                                    CreatedAt = reader.GetDateTime("created_at"),
                                    UpdatedAt = reader.GetDateTime("updated_at"),
                                    CommentCount = reader.GetInt32("comment_count"),
                                    LikeCount = reader.GetInt32("like_count"),
                                    IsLiked = false
                                });
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"搜索帖子失败: {ex.Message}");
                throw;
            }

            return posts;
        }

        /// <summary>
        /// 获取论坛统计数据
        /// </summary>
        public async Task<Dictionary<string, int>> GetForumStatsAsync()
        {
            var stats = new Dictionary<string, int>();

            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();

                    // 获取总帖子数
                    string postCountQuery = "SELECT COUNT(*) FROM forum_posts";
                    using (var cmd = new MySqlCommand(postCountQuery, connection))
                    {
                        var result = await cmd.ExecuteScalarAsync();
                        stats["totalPosts"] = Convert.ToInt32(result);
                    }

                    // 获取总用户数（发帖或评论过的）
                    string userCountQuery = @"
                        SELECT COUNT(DISTINCT user_id) 
                        FROM (
                            SELECT user_id FROM forum_posts WHERE user_id IS NOT NULL
                            UNION
                            SELECT user_id FROM forum_comments
                        ) as active_users";
                    using (var cmd = new MySqlCommand(userCountQuery, connection))
                    {
                        var result = await cmd.ExecuteScalarAsync();
                        stats["totalUsers"] = Convert.ToInt32(result);
                    }

                    // 获取今日新帖数
                    string todayPostsQuery = "SELECT COUNT(*) FROM forum_posts WHERE DATE(created_at) = CURDATE()";
                    using (var cmd = new MySqlCommand(todayPostsQuery, connection))
                    {
                        var result = await cmd.ExecuteScalarAsync();
                        stats["todayPosts"] = Convert.ToInt32(result);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取论坛统计数据失败: {ex.Message}");
                // 发生错误时返回默认值
                stats["totalPosts"] = 0;
                stats["totalUsers"] = 0;
                stats["todayPosts"] = 0;
            }

            return stats;
        }

        /// <summary>
        /// 创建帖子并添加图片
        /// </summary>
        public async Task<int> CreatePostWithTagsAndImagesAsync(string title, string content, List<string> tags, List<Dictionary<string, string>> images, int? userId = null)
        {
            int postId = 0;
            
            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();
                    
                    // 开始事务
                    using (var transaction = await connection.BeginTransactionAsync())
                    {
                        try
                        {
                            // 创建帖子
                            string insertPostQuery = @"
                                INSERT INTO forum_posts (user_id, title, content, view_count, created_at, updated_at)
                                VALUES (@userId, @title, @content, 0, @createdAt, @updatedAt);
                                SELECT LAST_INSERT_ID();";
                                
                            using (var cmd = new MySqlCommand(insertPostQuery, connection, transaction))
                            {
                                cmd.Parameters.AddWithValue("@userId", userId.HasValue ? (object)userId.Value : DBNull.Value);
                                cmd.Parameters.AddWithValue("@title", title);
                                cmd.Parameters.AddWithValue("@content", content);
                                cmd.Parameters.AddWithValue("@createdAt", DateTime.Now);
                                cmd.Parameters.AddWithValue("@updatedAt", DateTime.Now);
                                
                                postId = Convert.ToInt32(await cmd.ExecuteScalarAsync());
                            }
                            
                            // 处理标签
                            if (tags != null && tags.Count > 0)
                            {
                                foreach (var tagName in tags)
                                {
                                    // 检查标签是否已存在
                                    int tagId = 0;
                                    string checkTagQuery = "SELECT id FROM forum_tags WHERE name = @name";
                                    using (var cmd = new MySqlCommand(checkTagQuery, connection, transaction))
                                    {
                                        cmd.Parameters.AddWithValue("@name", tagName);
                                        var result = await cmd.ExecuteScalarAsync();
                                        if (result != null && result != DBNull.Value)
                                        {
                                            tagId = Convert.ToInt32(result);
                                        }
                                    }
                                    
                                    // 如果标签不存在，则创建
                                    if (tagId == 0)
                                    {
                                        string insertTagQuery = @"
                                            INSERT INTO forum_tags (name, count, created_at)
                                            VALUES (@name, 1, @createdAt);
                                            SELECT LAST_INSERT_ID();";
                                        
                                        using (var cmd = new MySqlCommand(insertTagQuery, connection, transaction))
                                        {
                                            cmd.Parameters.AddWithValue("@name", tagName);
                                            cmd.Parameters.AddWithValue("@createdAt", DateTime.Now);
                                            tagId = Convert.ToInt32(await cmd.ExecuteScalarAsync());
                                        }
                                    }
                                    
                                    // 创建帖子与标签的关联
                                    string insertPostTagQuery = @"
                                        INSERT INTO forum_post_tags (post_id, tag_id)
                                        VALUES (@postId, @tagId)";
                                    
                                    using (var cmd = new MySqlCommand(insertPostTagQuery, connection, transaction))
                                    {
                                        cmd.Parameters.AddWithValue("@postId", postId);
                                        cmd.Parameters.AddWithValue("@tagId", tagId);
                                        await cmd.ExecuteNonQueryAsync();
                                    }
                                }
                            }
                            
                            // 处理图片
                            if (images != null && images.Count > 0)
                            {
                                try
                                {
                                    // 获取应用程序根目录的绝对路径
                                    string rootDir = Directory.GetCurrentDirectory();
                                    Console.WriteLine($"应用程序根目录: {rootDir}");
                                    
                                    // 创建static目录（如果不存在）
                                    string staticDir = Path.Combine(rootDir, "static");
                                    Console.WriteLine($"静态文件目录: {staticDir}");
                                    if (!Directory.Exists(staticDir))
                                    {
                                        try
                                        {
                                            Directory.CreateDirectory(staticDir);
                                            Console.WriteLine("成功创建static目录");
                                        }
                                        catch (Exception ex)
                                        {
                                            Console.WriteLine($"创建static目录失败: {ex.Message}");
                                            throw;
                                        }
                                    }
                                    
                                    // 创建uploads目录（如果不存在）
                                    string uploadsDir = Path.Combine(staticDir, "uploads");
                                    Console.WriteLine($"上传文件目录: {uploadsDir}");
                                    if (!Directory.Exists(uploadsDir))
                                    {
                                        try
                                        {
                                            Directory.CreateDirectory(uploadsDir);
                                            Console.WriteLine("成功创建uploads目录");
                                        }
                                        catch (Exception ex)
                                        {
                                            Console.WriteLine($"创建uploads目录失败: {ex.Message}");
                                            throw;
                                        }
                                    }
                                    
                                    // 创建forum目录（如果不存在）
                                    string forumDir = Path.Combine(uploadsDir, "forum");
                                    Console.WriteLine($"论坛图片目录: {forumDir}");
                                    if (!Directory.Exists(forumDir))
                                    {
                                        try
                                        {
                                            Directory.CreateDirectory(forumDir);
                                            Console.WriteLine("成功创建forum目录");
                                        }
                                        catch (Exception ex)
                                        {
                                            Console.WriteLine($"创建forum目录失败: {ex.Message}");
                                            throw;
                                        }
                                    }
                                    
                                    foreach (var img in images)
                                    {
                                        try
                                        {
                                            // 从base64数据中提取实际的图片数据
                                            string[] parts = img["data"].Split(',');
                                            if (parts.Length != 2)
                                            {
                                                Console.WriteLine("图片数据格式不正确，缺少base64分隔符");
                                                continue; // 跳过格式不正确的数据
                                            }
                                            
                                            // 解码base64数据
                                            byte[] imgData;
                                            try
                                            {
                                                imgData = Convert.FromBase64String(parts[1]);
                                                Console.WriteLine($"成功解码图片数据，大小: {imgData.Length} 字节");
                                            }
                                            catch (Exception ex)
                                            {
                                                Console.WriteLine($"解码base64数据失败: {ex.Message}");
                                                continue; // 跳过解码失败的数据
                                            }
                                            
                                            // 生成唯一文件名
                                            string fileExt = GetFileExtFromMimeType(img["type"]);
                                            string fileName = $"{Guid.NewGuid()}{fileExt}";
                                            string filePath = Path.Combine(forumDir, fileName);
                                            Console.WriteLine($"准备保存图片到: {filePath}");
                                            
                                            // 写入文件
                                            try
                                            {
                                                File.WriteAllBytes(filePath, imgData);
                                                Console.WriteLine($"成功保存图片: {filePath}");
                                                
                                                // 检查文件是否确实已创建
                                                if (File.Exists(filePath))
                                                {
                                                    Console.WriteLine($"确认文件已创建: {filePath}");
                                                    Console.WriteLine($"文件大小: {new FileInfo(filePath).Length} 字节");
                                                }
                                                else
                                                {
                                                    Console.WriteLine($"警告: 文件似乎未被创建: {filePath}");
                                                }
                                            }
                                            catch (Exception ex)
                                            {
                                                Console.WriteLine($"保存图片失败: {ex.Message}");
                                                Console.WriteLine($"异常类型: {ex.GetType().Name}");
                                                Console.WriteLine($"堆栈跟踪: {ex.StackTrace}");
                                                continue; // 跳过写入失败的文件
                                            }
                                            
                                            // 创建数据库记录
                                            string insertImageQuery = @"
                                                INSERT INTO forum_post_images (post_id, image_path, created_at)
                                                VALUES (@postId, @imagePath, @createdAt)";
                                            
                                            using (var cmd = new MySqlCommand(insertImageQuery, connection, transaction))
                                            {
                                                cmd.Parameters.AddWithValue("@postId", postId);
                                                cmd.Parameters.AddWithValue("@imagePath", $"/static/uploads/forum/{fileName}");
                                                cmd.Parameters.AddWithValue("@createdAt", DateTime.Now);
                                                await cmd.ExecuteNonQueryAsync();
                                                Console.WriteLine($"成功将图片信息插入数据库: {fileName}");
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            Console.WriteLine($"处理单个图片时发生错误: {ex.Message}");
                                        }
                                    }
                                }
                                catch (Exception ex)
                                {
                                    Console.WriteLine($"处理图片上传过程中发生错误: {ex.Message}");
                                    throw;
                                }
                            }
                            
                            // 提交事务
                            await transaction.CommitAsync();
                        }
                        catch (Exception ex)
                        {
                            // 回滚事务
                            await transaction.RollbackAsync();
                            Console.WriteLine($"创建帖子失败: {ex.Message}");
                            throw;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"创建帖子失败: {ex.Message}");
                throw;
            }
            
            return postId;
        }

        /// <summary>
        /// 根据MIME类型获取文件扩展名
        /// </summary>
        private string GetFileExtFromMimeType(string mimeType)
        {
            switch (mimeType)
            {
                case "image/jpeg":
                    return ".jpg";
                case "image/png":
                    return ".png";
                case "image/gif":
                    return ".gif";
                case "image/webp":
                    return ".webp";
                default:
                    return ".jpg"; // 默认扩展名
            }
        }
        
        /// <summary>
        /// 获取帖子的图片
        /// </summary>
        public async Task<List<ForumImage>> GetPostImagesAsync(int postId)
        {
            var images = new List<ForumImage>();
            
            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();
                    
                    string query = @"
                        SELECT id, post_id, image_path, created_at
                        FROM forum_post_images
                        WHERE post_id = @postId
                        ORDER BY created_at ASC";
                        
                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@postId", postId);
                        
                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                images.Add(new ForumImage
                                {
                                    Id = reader.GetInt32("id"),
                                    PostId = reader.GetInt32("post_id"),
                                    ImagePath = reader.GetString("image_path"),
                                    CreatedAt = reader.GetDateTime("created_at")
                                });
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取帖子图片失败: {ex.Message}");
                throw;
            }
            
            return images;
        }

        /// <summary>
        /// 测试文件系统权限
        /// </summary>
        public void TestFileSystemPermissions()
        {
            try
            {
                // 测试static目录
                string staticDir = Path.Combine(Directory.GetCurrentDirectory(), "static");
                Console.WriteLine($"测试static目录权限: {staticDir}");
                
                if (!Directory.Exists(staticDir))
                {
                    try
                    {
                        Directory.CreateDirectory(staticDir);
                        Console.WriteLine("成功创建static目录");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"创建static目录失败: {ex.Message}");
                    }
                }
                else
                {
                    Console.WriteLine("static目录已存在");
                }
                
                // 测试uploads目录
                string uploadsDir = Path.Combine(staticDir, "uploads");
                Console.WriteLine($"测试uploads目录权限: {uploadsDir}");
                
                if (!Directory.Exists(uploadsDir))
                {
                    try
                    {
                        Directory.CreateDirectory(uploadsDir);
                        Console.WriteLine("成功创建uploads目录");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"创建uploads目录失败: {ex.Message}");
                    }
                }
                else
                {
                    Console.WriteLine("uploads目录已存在");
                }
                
                // 测试forum目录
                string forumDir = Path.Combine(uploadsDir, "forum");
                Console.WriteLine($"测试forum目录权限: {forumDir}");
                
                if (!Directory.Exists(forumDir))
                {
                    try
                    {
                        Directory.CreateDirectory(forumDir);
                        Console.WriteLine("成功创建forum目录");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"创建forum目录失败: {ex.Message}");
                    }
                }
                else
                {
                    Console.WriteLine("forum目录已存在");
                }
                
                // 测试写入权限
                string testFile = Path.Combine(forumDir, "test.txt");
                try
                {
                    File.WriteAllText(testFile, "测试写入权限");
                    Console.WriteLine($"成功写入测试文件: {testFile}");
                    
                    // 测试读取权限
                    string content = File.ReadAllText(testFile);
                    Console.WriteLine($"成功读取测试文件: {content}");
                    
                    // 测试删除权限
                    File.Delete(testFile);
                    Console.WriteLine("成功删除测试文件");
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"文件操作失败: {ex.Message}");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"测试文件系统权限时发生错误: {ex.Message}");
            }
        }

        /// <summary>
        /// 创建用户惩罚记录（禁言或封号）
        /// </summary>
        public async Task<int> CreateUserPunishmentAsync(int userId, string punishmentType, int durationDays, string reason, int? relatedReportId = null)
        {
            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();

                    // 创建用户惩罚表（如果不存在）
                    string createTableQuery = @"
                        CREATE TABLE IF NOT EXISTS user_punishments (
                            id INT PRIMARY KEY AUTO_INCREMENT,
                            user_id INT NOT NULL,
                            punishment_type VARCHAR(50) NOT NULL,
                            start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                            end_date TIMESTAMP,
                            reason TEXT,
                            related_report_id INT,
                            created_by INT,
                            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                            FOREIGN KEY (related_report_id) REFERENCES forum_reports(id) ON DELETE SET NULL,
                            FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
                        )";

                    using (var cmd = new MySqlCommand(createTableQuery, connection))
                    {
                        await cmd.ExecuteNonQueryAsync();
                    }

                    // 计算结束时间
                    DateTime endDate = DateTime.Now.AddDays(durationDays);

                    // 插入惩罚记录
                    string query = @"
                        INSERT INTO user_punishments 
                        (user_id, punishment_type, end_date, reason, related_report_id, created_by)
                        VALUES 
                        (@userId, @punishmentType, @endDate, @reason, @relatedReportId, @createdBy);
                        SELECT LAST_INSERT_ID();";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@userId", userId);
                        cmd.Parameters.AddWithValue("@punishmentType", punishmentType);
                        cmd.Parameters.AddWithValue("@endDate", endDate);
                        cmd.Parameters.AddWithValue("@reason", reason);
                        cmd.Parameters.AddWithValue("@relatedReportId", relatedReportId ?? (object)DBNull.Value);
                        
                        // 此处假设管理员ID为1，实际应从Session中获取
                        int adminId = 1; // HttpContext.Session.GetInt32("AdminId").Value;
                        cmd.Parameters.AddWithValue("@createdBy", adminId);

                        var result = await cmd.ExecuteScalarAsync();
                        return Convert.ToInt32(result);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"创建用户惩罚记录失败: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 检查用户是否被禁言或封号
        /// </summary>
        public async Task<UserPunishmentStatus> CheckUserPunishmentStatusAsync(int userId)
        {
            var result = new UserPunishmentStatus
            {
                IsMuted = false,
                IsBanned = false,
                MuteEndDate = null,
                BanEndDate = null,
                MuteReason = null,
                BanReason = null
            };

            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();

                    string query = @"
                        SELECT 
                            punishment_type, end_date, reason
                        FROM 
                            user_punishments
                        WHERE 
                            user_id = @userId
                            AND end_date > NOW()
                        ORDER BY 
                            end_date DESC";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@userId", userId);

                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                string punishmentType = reader.GetString("punishment_type");
                                DateTime endDate = reader.GetDateTime("end_date");
                                string reason = reader.IsDBNull("reason") ? null : reader.GetString("reason");

                                if (punishmentType == "mute" && !result.IsMuted)
                                {
                                    result.IsMuted = true;
                                    result.MuteEndDate = endDate;
                                    result.MuteReason = reason;
                                }
                                else if (punishmentType == "ban" && !result.IsBanned)
                                {
                                    result.IsBanned = true;
                                    result.BanEndDate = endDate;
                                    result.BanReason = reason;
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"检查用户禁言状态失败: {ex.Message}");
                throw;
            }

            return result;
        }

        /// <summary>
        /// 获取用户惩罚历史
        /// </summary>
        public async Task<List<UserPunishment>> GetUserPunishmentsAsync(int userId)
        {
            var punishments = new List<UserPunishment>();

            try
            {
                using (var connection = GetConnection())
                {
                    await connection.OpenAsync();

                    string query = @"
                        SELECT 
                            p.id, p.user_id, p.punishment_type, p.start_date, 
                            p.end_date, p.reason, p.related_report_id, 
                            p.created_by, a.username as admin_name, p.created_at
                        FROM 
                            user_punishments p
                        LEFT JOIN 
                            users a ON p.created_by = a.id
                        WHERE 
                            p.user_id = @userId
                        ORDER BY 
                            p.created_at DESC";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@userId", userId);

                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                punishments.Add(new UserPunishment
                                {
                                    Id = reader.GetInt32("id"),
                                    UserId = reader.GetInt32("user_id"),
                                    PunishmentType = reader.GetString("punishment_type"),
                                    StartDate = reader.GetDateTime("start_date"),
                                    EndDate = reader.GetDateTime("end_date"),
                                    Reason = reader.IsDBNull("reason") ? null : reader.GetString("reason"),
                                    RelatedReportId = reader.IsDBNull("related_report_id") ? (int?)null : reader.GetInt32("related_report_id"),
                                    CreatedBy = reader.GetInt32("created_by"),
                                    AdminName = reader.IsDBNull("admin_name") ? null : reader.GetString("admin_name"),
                                    CreatedAt = reader.GetDateTime("created_at"),
                                    IsActive = reader.GetDateTime("end_date") > DateTime.Now
                                });
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取用户惩罚历史失败: {ex.Message}");
                throw;
            }

            return punishments;
        }
    }
}
